<?php

setlocale(LC_MONETARY, 'en_US');
require_once "../funciones.php";
require_once '../funciones/JSON.php';
autenticado();

$caweb = con_intranetpccom();

$examp = $_REQUEST["q"]; //query number


switch ($examp) {
    case 1:
        $page = $_REQUEST['page']; // get the requested page
        $limit = $_REQUEST['rows']; // get how many rows we want to have into the grid
        $sidx = $_REQUEST['sidx']; // get index row - i.e. user click to sort
        $sord = $_REQUEST['sord']; // get the direction
        
        //recibe los parametros de la busqueda
        $usuario = $_SESSION['caweb']['username'];
        
        if (!$sidx)
            $sidx = 1;


        $wh = "";
        $searchOn = Strip($_REQUEST['_search']);
        if ($searchOn == 'true') {
            $searchstr = Strip($_REQUEST['filters']);
            $wh = constructWhere($searchstr);
            //echo $wh;
        }

        //Consulta requerimientos Creados o Pausados
        $result = mysql_query("select max(sol.solRqId),solRqUsr from requerimientos req
                inner join solucionesRq sol on sol.reqId = req.reqId
                where estRqId <> 3  and sol.solRqId in (select max(solaux.solRqId) from solucionesRq solaux where solaux.reqId = req.reqId)
                group by req.reqId". $wh." having sol.solRqUsr = '".$_SESSION['caweb']['username']."'")  or die("Could not execute query." . mysql_error());
        //$row = mysql_fetch_array($result, MYSQL_ASSOC);
        $count = mysql_num_rows($result);

        if ($count > 0) {
            $total_pages = ceil($count / $limit);
        } else {
            $total_pages = 0;
        }
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        if ($start < 0)
            $start = 0;
        $SQL = "select req.reqFec,req.reqUsr,req.reqId,solRqUsr,solRqId from requerimientos req
                inner join solucionesRq sol on sol.reqId = req.reqId
                where estRqId <> 3  and sol.solRqId in (select max(solaux.solRqId) from solucionesRq solaux where solaux.reqId = req.reqId)
                group by req.reqId having sol.solRqUsr = '".$_SESSION['caweb']['username']. "' ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $result = mysql_query($SQL) or die("Could not execute query." . mysql_error());

        if (stristr($_SERVER["HTTP_ACCEPT"], "application/xhtml+xml")) {
            header("Content-type: application/xhtml+xml;charset=utf-8");
        } else {
            header("Content-type: text/xml;charset=utf-8");
        }
        $et = ">";
        echo "<?xml version='1.0' encoding='utf-8'?$et\n";
        echo "<rows>";
        echo "<page>" . $page . "</page>";
        echo "<total>" . $total_pages . "</total>";
        echo "<records>" . $count . "</records>"; // be sure to put text data in CDATA$sql
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            echo "<row id='" . $row['reqId'] . "'>";
            echo "<cell>" . $row['reqId'] . "</cell>";
            echo "<cell>" . strtoupper($row['reqFec']) . "</cell>";
            echo "<cell>" . strtoupper($row['reqUsr']) . "</cell>";
            //Se adquiere la informacion de la empresa
            $sql = "select entrada from usuarios where username = '".$row['reqUsr']."'";
            $tmp = mysql_query($sql) or die("Error Usuario ".  mysql_error().$sql);
            $tmp = mysql_fetch_array($tmp);
            echo "<cell>" . strtoupper($tmp['entrada']) . "</cell>";
            echo "</row>";
        }
        echo "</rows>";
        break;


    case 2:
        //Ubico la informacion del usuario para poderla editar
        $id = trim($_REQUEST['reqId']);
        $sql = "select * from requerimientos where reqId = $id";
        $result = mysql_query($sql) or die("Could not execute query." . mysql_error());;
        $requerimiento = mysql_fetch_array($result,MYSQL_ASSOC);
        //Informacion del sistema
        $sql = "select sisNombre from sistemas where sisId = ".$requerimiento['sisId'];
        $result = mysql_query($sql) or die("Could not execute query." . mysql_error());;
        $sistema = mysql_fetch_array($result,MYSQL_ASSOC);
        //Informacion de la empresa
        $sql = "select entrada from usuarios where username = '".$requerimiento['reqUsr']."'";
        $tmp = mysql_query($sql) or die("Could not execute query." . mysql_error());;
        $empresa = mysql_fetch_array($tmp,MYSQL_ASSOC);
        //Estado del requerimiento
        $sql = "select max(solRqId),solRqDes,est.estRqNombre from solucionesRq sol ";
        $sql .=  "inner join estadosRq est on est.estRqId = sol.estRqId ";
        $sql .=  "where sol.estRqId = 2 and sol.reqId = ".$id."  having max(solRqId) is not null";
        $result1 = mysql_query($sql) or die("Could not execute query." . mysql_error());;
        $row1 = mysql_fetch_array($result1,MYSQL_ASSOC);
        if(is_array($row1)){
                $estado = $row['estRqNombre'];
                $respuesta = $row1['solRqDes'];
        }
        else{
            $estado = "Creado";
            $respuesta = "";
        }
        $archivo = ($requerimiento["reqArc"] == "")? "nada": $requerimiento["reqArc"];
        //Organiza el Objeto JSON para entregar
        $json = "{";
        $json .= "'reqId': '{$id}',";
        $json .= "'usuario': '".utf8_encode($requerimiento["reqUsr"])."',";
        $json .= "'fecha': '{$requerimiento["reqFec"]}',";
        $json .= "'descripcion': '".utf8_encode(htmlspecialchars($requerimiento["reqDes"]))."',";
        $json .= "'sistema': '{$sistema["sisNombre"]}',";
        $json .= "'archivo': '$archivo',";
        $json .= "'estado':    '{$estado}',"; 
        $json .= "'respuesta': '{$respuesta}'}";
        $sale = str_replace("'", "\"", $json);
        echo "$sale";
        break;
        
    //envia el correo a la persona que se le asigno el requerimiento
    case 3:
        require_once("../phpmailer/class.phpmailer.php");
        $reqId = $_POST['reqId'];
        $estado = $_POST['estado'];
        $descripcion = $_REQUEST['descEstado'];
        $tipRqId = $_REQUEST['tipo'];
        //Se escala el requerimiento o lo cierra
        if($estado == 6 || $estado == 3 || $estado == 7){
            if($estado == 6){ // Si escala el requerimiento
                $responsable = $_REQUEST['responsable'];
                //optengo mail del responsable que se asigno
                $sql = "select user_email from usuarios where username = '$responsable' ";
                $realiza = $responsable;
                $result = mysql_query($sql) or die("Error sentencia SQL ".mysql_error());
                $datosResponsable = mysql_fetch_array($result);
                $correoResponsable = $datosResponsable['user_email'];
                $describeCorreo = "Le ha sido escalado la incidencia No $reqId, motivo <br /> $descripcion.";
                $subjetCorreo = "Escalado Incidencia $reqId";
                //ubica id de la solucion
                $sql = "select max(solRqId) as ID from solucionesRq where reqId = $reqId";
                $result = mysql_query($sql) or die("Error No existen reportes para la Incidencia $reqId ".mysql_error());
                $result = mysql_fetch_array($result);
                $solID = $result['ID'];
            }
            elseif($estado == 3){ //concluye incidencia
                $realiza = $_SESSION['caweb']['username'];
                $responsable = $_REQUEST['reqUsr'];
                //optengo mail del responsable que se asigno
                $sql = "select reporta_email,user_email from usuarios where username = '$responsable' ";
                $result = mysql_query($sql) or die("Error sentencia SQL ".mysql_error());
                $datosResponsable = mysql_fetch_array($result);
                $correoResponsable = ($datosResponsable['user_email'] != "")? $datosResponsable['user_email'] : $datosResponsable['reporta_email'] ;
                $describeCorreo = "Respuesta, <br /> $descripcion.";
                $subjetCorreo = "Solucion incidencia No $reqId";
            }
            else{ //solicita informacion
                //optengo mail del responsable que se asigno
                $realiza = $_SESSION['caweb']['username'];
                $sql = "select reporta_email,user_email from usuarios where username = '$realiza' ";
                $result = mysql_query($sql) or die("Error sentencia SQL ".mysql_error());
                $datosIngeniero = mysql_fetch_array($result);

                //optengo mail del usuario que genero la incidencia
                $responsable = $_REQUEST['reqUsr'];
                $sql = "select reporta_email,user_email from usuarios where username = '$responsable' ";
                $result = mysql_query($sql) or die("Error sentencia SQL ".mysql_error());
                $datosResponsable = mysql_fetch_array($result);
                $correoResponsable = ($datosResponsable['user_email'] != "")? $datosResponsable['user_email'] : $datosResponsable['reporta_email'] ;
                $describeCorreo = $descripcion."<br />Favor responder este correo a ".$datosIngeniero['user_email'];
                $subjetCorreo = "Informacion adicional Incidencia No $reqId";
            }
            register_shutdown_function(enviaCorreo($correoResponsable,$subjetCorreo,$describeCorreo));
        }
        else
            $realiza = $_SESSION['caweb']['username'];
        if($estado == 6) //Escala la incidencia por tal motivo inserta mas informacion
            $sql = "insert into solucionesRq(solRqUsr,solRqDes,solRqFec,reqId,estRqId,tipRqId,solRqEsc)
                            values('$realiza','$descripcion',now(),$reqId,$estado,$tipRqId,$solID)";
        else
            $sql = "insert into solucionesRq(solRqUsr,solRqDes,solRqFec,reqId,estRqId,tipRqId)
                            values('$realiza','$descripcion',now(),$reqId,$estado,$tipRqId)";
        $result = mysql_query($sql) or die("Error sentencia SQL ".mysql_error()."<br />".$sql);
        echo $reqId;
        break;

    //Alimenta la grilla auxiliar
    case 4:
        $page = $_REQUEST['page']; // get the requested page
        $limit = $_REQUEST['rows']; // get how many rows we want to have into the grid
        $sidx = $_REQUEST['sidx']; // get index row - i.e. user click to sort
        $sord = $_REQUEST['sord']; // get the direction
       
        //recibe los parametros de la busqueda
        $reqId = $_REQUEST['reqId']; //numero del requerimiento
        $usuario = $_SESSION['caweb']['username'];

        if (!$sidx)
            $sidx = 1;



        //Consulta requerimientos Creados o Pausados
        $result = mysql_query("select count(*) from solucionesRq
                where reqId = $reqId ")  or die("Could not execute query." . mysql_error());
        //$row = mysql_fetch_array($result, MYSQL_ASSOC);
        $count = mysql_num_rows($result);

        if ($count > 0) {
            $total_pages = ceil($count / $limit);
        } else {
            $total_pages = 0;
        }
        if ($page > $total_pages)
            $page = $total_pages;
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        if ($start < 0)
            $start = 0;
        $SQL = "select solRqFec,solRqDes,solRqUsr,solRqId, est.estRqNombre from solucionesRq sol
                inner join estadosRq est on sol.estRqId = est.estRqId where reqId = $reqId ORDER BY " . $sidx . " " . $sord . " LIMIT " . $start . " , " . $limit;
        $result = mysql_query($SQL) or die("Could not execute query." . mysql_error());

        if (stristr($_SERVER["HTTP_ACCEPT"], "application/xhtml+xml")) {
            header("Content-type: application/xhtml+xml;charset=utf-8");
        } else {
            header("Content-type: text/xml;charset=utf-8");
        }
        $et = ">";
        echo "<?xml version='1.0' encoding='utf-8'?$et\n";
        echo "<rows>";
        echo "<page>" . $page . "</page>";
        echo "<total>" . $total_pages . "</total>";
        echo "<records>" . $count . "</records>"; // be sure to put text data in CDATA$sql
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            echo "<row id='" . $row['solRqId'] . "'>";
            echo "<cell>" . $row['solRqFec'] . "</cell>";
            echo "<cell>" . strtoupper($row['solRqUsr']) . "</cell>";
            echo "<cell>" . utf8_encode($row['solRqDes']) . "</cell>";
            echo "<cell>" . strtoupper($row['estRqNombre']) . "</cell>";
            echo "</row>";
        }
        echo "</rows>";
        break;


    default :
        break;
}
mysql_close($db);

function Strip($value) {
    if (get_magic_quotes_gpc() != 0) {
        if (is_array($value))
            if (array_is_associative($value)) {
                foreach ($value as $k => $v)
                    $tmp_val[$k] = stripslashes($v);
                $value = $tmp_val;
            }
            else
                for ($j = 0; $j < sizeof($value); $j++)
                    $value[$j] = stripslashes($value[$j]);
        else
            $value = stripslashes($value);
    }
    return $value;
}

function array_is_associative($array) {
    if (is_array($array) && !empty($array)) {
        for ($iterator = count($array) - 1; $iterator; $iterator--) {
            if (!array_key_exists($iterator, $array)) {
                return true;
            }
        }
        return!array_key_exists(0, $array);
    }
    return false;
}

function ToSql($field, $oper, $val) {
    // we need here more advanced checking using the type of the field - i.e. integer, string, float
    switch ($field) {
        case 'id':
            return intval($val);
            break;
        case 'amount':
        case 'tax':
        case 'total':
            return floatval($val);
            break;
        default :
            //mysql_real_escape_string is better
            if ($oper == 'bw' || $oper == 'bn')
                return "'" . addslashes($val) . "%'";
            else if ($oper == 'ew' || $oper == 'en')
                return "'%" . addcslashes($val) . "'";
            else if ($oper == 'cn' || $oper == 'nc')
                return "'%" . addslashes($val) . "%'";
            else
                return "'" . addslashes($val) . "'";
    }
}

function constructWhere($s) {
    $json = new Services_JSON();
    $qwery = "";
    //['eq','ne','lt','le','gt','ge','bw','bn','in','ni','ew','en','cn','nc']
    $qopers = array(
        'eq' => " = ",
        'ne' => " <> ",
        'lt' => " < ",
        'le' => " <= ",
        'gt' => " > ",
        'ge' => " >= ",
        'bw' => " LIKE ",
        'bn' => " NOT LIKE ",
        'in' => " IN ",
        'ni' => " NOT IN ",
        'ew' => " LIKE ",
        'en' => " NOT LIKE ",
        'cn' => " LIKE ",
        'nc' => " NOT LIKE ");
    if ($s) {
        $prueba = $json->decode($s);
        $jsona = objecto2array($prueba);
        //$jsona = json_decode($s,true);
        if (is_array($jsona)) {
            $gopr = $jsona['groupOp'];
            $rules = $jsona['rules'];
            $i = 0;
            foreach ($rules as $key => $val) {
                $field = $val['field'];
                $op = $val['op'];
                $v = $val['data'];
                if ($v && $op) {
                    $i++;
                    // ToSql in this case is absolutley needed
                    $v = ToSql($field, $op, $v);
                    if ($i == 1)
                        $qwery = " AND ";
                    else
                        $qwery .= " " . $gopr . " ";
                    switch ($op) {
                        // in need other thing
                        case 'in' :
                        case 'ni' :
                            $qwery .= $field . $qopers[$op] . " (" . $v . ")";
                            break;
                        default:
                            $qwery .= $field . $qopers[$op] . $v;
                    }
                }
            }
        }
    }
    return $qwery;
}

//Funcion para convertir un objeto en arreglo
function objecto2array($object) {
    if (is_array($object) || is_object($object)) {
        $array = array();
        foreach ($object as $key => $value) {
            $array[$key] = objecto2array($value);
        }
        return $array;
    }
    return $object;
}

//funcion para el envio de correos
function enviaCorreo($correoResponsable,$subjetCorreo,$describeCorreo){
    $mail = new PHPMailer();
    $mail->From     = "analista1@pc.com.co";
    $mail->FromName = "Sistema S.O.S";

    $mail->AddAddress($correoResponsable);
    $mail->WordWrap = 50;                                   // Largo de las lineas
    $mail->IsHTML(true);                                    // Podemos incluir tags html
    $mail->Subject  =  $subjetCorreo;
    $mail->Body     =  $describeCorreo;
    $mail->AltBody  =  strip_tags($mail->Body);             // Este es el contenido alternativo sin html
    //$mail->AddAttachment("/resultados/resultado.csv");      // Ingresamos la ruta del archivo

    $mail->IsSMTP();
    $mail->Host       = "ssl://smtp.gmail.com";
    $mail->SMTPDebug  = 1;
    $mail->SMTPAuth   = true;
    $mail->Port       = 465;
    $mail->Username   = "analista1@pc.com.co";
    $mail->Password   = "leugim";
    $mail->Send();
}
?>